﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_G_Sort]
	(
		@CompanyId VARCHAR(500),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@sort VARCHAR(3000) 
	)
AS
DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max),@strSql2 VARCHAR(max),@strSql3 VARCHAR(max),@sWhere varchar(500)
--(Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh
	set @sWhere=''
	
	if @CompanyId<>'' and @CompanyId<>'0'	
	begin 
		set @sWhere=' Where Id in ('+@CompanyId+')'
	end 
	
	SET @strSql='

	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	Set @eRq_s = Convert(Datetime, '''+@EndDate + ' 00:00:00'')
	
	
	;WITH list As(Select *,ROW_NUMBER() OVER (ORDER BY '+@sort+')AS Row from (select 
	c.Id As CompanyId
	,c.CompanyName,dwbh,Mid
	,ISNULL((Select Sum(OAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OAA--直接成本
	,ISNULL((Select Sum(OAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OAB--间接成本
	,ISNULL((Select Sum(OBA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBA--市场费用
	,ISNULL((Select Sum(OBB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBB--日常生活
	,ISNULL((Select Sum(OBC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBC--行政办公
	,ISNULL((Select Sum(OBD) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBD--财务费用
	,ISNULL((Select Sum(OBE) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBE--人力费用
	,ISNULL((Select Sum(OBF) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBF--部门基金
	,ISNULL((Select Sum(OBG) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBG--总办费用
	,ISNULL((Select Sum(OBH) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OBH--其他费用
	,ISNULL((Select Sum(OCA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OCA--投资
	,ISNULL((Select Sum(OCB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OCB--借款
	,ISNULL((Select Sum(OCC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As OCC--往来借款
	,ISNULL((Select Sum(ODA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As ODA--有形资产
	,ISNULL((Select Sum(ODB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As ODB--无形资产
	,ISNULL((Select Sum(IAA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAA--新增毛利
	,ISNULL((Select Sum(IAB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAB--续费毛利
	
	
	--,ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From  [Order] Where CompanyId=c.Id and AuditDate>=@sRq and AuditDate <=@eRq  AND StateId>=1 AND (NOE_Flag<>2) ),0)+
	--		ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=c.Id and UpTime>=@sRq and UpTime <=@eRq and o.companyId=c.Id AND o.NOE_Flag<>2),0) As IAC--新增应收 AND (NOE_Flag<>2)|and oid IN (SELECT oid FROM [Order] o WHERE companyId=c.Id AND o.NOE_Flag<>2 )
	,ISNULL((Select Sum(IAC) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq 
	and CreateDate<=@eRq),0) as IAC --新增应收2014.12.30 查询历史记录，不再实时统计
	
	,ISNULL((Select Sum(CostPrice) From Project Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq and FinanceAudit=1 ),0)+
			ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=c.Id and UpTime>=@sRq and UpTime <=@eRq ),0) As IAD--新增应付
	 '
	 SET @strSql1='
	 ,ISNULL((Select Sum(IAE) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq ),0) As IAE--成单金额
	,ISNULL((Select Sum(IAF) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAF--尾款收入
	,ISNULL((Select Sum(IAG) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IAG--非营利性收入
		                                                                                                                                                     
	,ISNULL((Select IBA From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBA--实有现金
	,ISNULL((Select IBB From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBB--实有银行
	,ISNULL((Select IBC From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBC--账户余额
	,ISNULL((Select Top 1 IBD From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBD--应有现金
	                                                                                                                                      --
	,ISNULL((Select Sum(IBDA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBDA--现金收入
	,ISNULL((Select Sum(IBDB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBDB--现金支出
	,ISNULL((Select Top 1 IBE From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBE--应有银行
	,ISNULL((Select Sum(IBEA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBEA--银行收入
	,ISNULL((Select Sum(IBEB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBEB--银行支出
	,ISNULL((Select Top 1 IBF From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBF--借款合计
	,ISNULL((Select Top 1 IBG From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBG--资金合计
	,ISNULL((Select Sum(IBH) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBH--应有现金平整
	,ISNULL((Select Sum(IBHA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBHA--现金平整收入
	,ISNULL((Select Sum(IBHB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBHB--现金平整支出
	,ISNULL((Select Sum(IBI) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBI--应有银行平整
	,ISNULL((Select Sum(IBIA) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBIA--银行平整收入
	,ISNULL((Select Sum(IBIB) From Statistics_Financial Where CompanyId=c.Id and CreateDate >=@sRq and CreateDate<=@eRq),0) As IBIB--银行平整支出
	,ISNULL((Select sum(ibj) From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBJ--差额
	'SET @strSql2=',ISNULL((Select IBK From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBK--应收合计
	,ISNULL((Select IBL From Statistics_Financial Where CompanyId=c.Id and CreateDate>=@eRq_s and CreateDate <=@eRq),0) As IBL--应付合计
	From Company c  '+@sWhere+') as nb
	)
	
	
	Select * From list,(SELECT SUM(IAA) AS IAA_a,SUM(IAB) AS IAB_a,SUM(IAC) AS IAC_a,SUM(IAD) AS IAD_a,SUM(IAE) AS IAE_a,
	SUM(IAF) AS IAF_a,SUM(IAG) AS IAG_a 
	
	,SUM(OAA) AS OAA_a,SUM(OAB) AS OAB_a,SUM(OBA) AS OBA_a,SUM(OBB) AS OBB_a
	,SUM(OBC) AS OBC_a,SUM(OBD) AS OBD_a,SUM(OBE) AS OBE_a,SUM(OBF) AS OBF_a
	,SUM(OBG) AS OBG_a,SUM(OBH) AS OBH_a,SUM(OCA) AS OCA_a,SUM(OCB) AS OCB_a,SUM(OCC) AS OCC_a
	,SUM(ODA) AS ODA_a,SUM(ODB) AS ODB_a,
	
	SUM(IBA) AS IBA_a,SUM(IBB) AS IBB_a,SUM(IBD) AS IBD_a,SUM(IBE) AS IBE_a
	,SUM(IBC) AS IBC_a,SUM(IBF) AS IBF_a,SUM(IBG) AS IBG_a,SUM(IBH) AS IBH_a
	,SUM(IBI) AS IBI_a,SUM(IBJ) AS IBJ_a,SUM(IBK) AS IBK_a,SUM(IBL) AS IBL_a
	 FROM list) AS n 
	 '
	 SET @strSql3=' ORDER BY '+@sort+''
	-- PRINT (@strSql+@strSql1+@strSql2)
EXEC(@strSql+@strSql1+@strSql2+@strSql3)

	RETURN
